package com.letu.core.utils;

import java.util.List;

/**
 * 组装特殊排序sql
 * Date:2021/9/3
 * Time:14:02
 *
 * @author zjn
 */
public class AssembleOrderBySqlUtils {

    public static final String SQL_SPACE = " ";
    public static final String SQL_ORDER = "ORDER";
    public static final String SQL_BY = "BY";
    public static final String SQL_FIELD = "FIELD";
    public static final String SQL_LEFT_BRACKET = "(";
    public static final String SQL_RIGHT_BRACKET = ")";
    public static final String SQL_COMMA = ",";
    public static final String SQL_SINGLE_QUOTATION_MARK = "'";
    public static final String SQL_CONVERT = "CONVERT";
    public static final String SQL_IF = "IF";
    public static final String SQL_IS = "IS";
    public static final String SQL_NULL = "NULL";
    public static final String SQL_OR = "OR";
    public static final String SQL_EQUAL = "=";
    public static final String SQL_USING = "USING";
    public static final String SQL_GBK = "gbk";
    public static final String SQL_ASC = "asc";
    public static final String SQL_DESC = "desc";

    /**
     * in条件拼接排序
     * ORDER BY FIELD (id，1,2,3,4）
     *
     * @param sortColumn 排序列名
     * @param source     排序的数据
     * @param <E>
     * @return 排序sql
     */
    public static <E> String assembleField(String sortColumn, List<E> source) {
        if (source == null || source.isEmpty()) {
            return "";
        }
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(SQL_ORDER).append(SQL_SPACE).append(SQL_BY).append(SQL_SPACE).append(SQL_FIELD)
                .append(SQL_SPACE).append(SQL_LEFT_BRACKET).append(SQL_SPACE).append(sortColumn).append(SQL_COMMA)
                .append(SQL_SPACE);
        for (E e : source) {
            if (e.getClass().equals(String.class)) {
                stringBuilder.append(SQL_SINGLE_QUOTATION_MARK).append(e).append(SQL_SINGLE_QUOTATION_MARK)
                        .append(SQL_COMMA).append(SQL_SPACE);
            }
            if (e.getClass().equals(Long.class) || e.getClass().equals(Integer.class)) {
                stringBuilder.append(e).append(SQL_COMMA).append(SQL_SPACE);
            }
        }
        String sql = stringBuilder.toString();
        sql = sql.substring(0, sql.length() - 2) + SQL_RIGHT_BRACKET;
        return sql;
    }

    /**
     * convert gbk升序排序
     * ORDER BY convert(name using gbk) asc
     * ORDER BY convert(if(name is null or name = '', en_name, name) using gbk) asc
     *
     * @param mainColumn      主列
     * @param alternateColumn 备用列
     * @param sortType        排序方式ASC/DESC
     * @return 排序sql
     */
    public static String assembleConvert(String mainColumn, String alternateColumn, String sortType) {
        if (StringUtils.isBlank(mainColumn)) {
            return "";
        }

        if (SQL_ASC.equalsIgnoreCase(sortType)) {
            sortType = SQL_ASC;
        } else {
            sortType = SQL_DESC;
        }

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(SQL_ORDER).append(SQL_SPACE).append(SQL_BY).append(SQL_SPACE).append(SQL_CONVERT)
                .append(SQL_SPACE).append(SQL_LEFT_BRACKET);
        if (StringUtils.isBlank(alternateColumn)) {
            stringBuilder.append(mainColumn).append(SQL_SPACE).append(SQL_USING).append(SQL_SPACE).append(SQL_GBK)
                    .append(SQL_RIGHT_BRACKET).append(SQL_SPACE).append(sortType);
        } else {
            stringBuilder.append(SQL_IF).append(SQL_LEFT_BRACKET).append(mainColumn)
                    .append(SQL_SPACE).append(SQL_IS).append(SQL_SPACE).append(SQL_NULL).append(SQL_SPACE)
                    .append(SQL_OR).append(SQL_SPACE).append(mainColumn).append(SQL_EQUAL).append(SQL_SINGLE_QUOTATION_MARK)
                    .append(SQL_SINGLE_QUOTATION_MARK).append(SQL_COMMA).append(SQL_SPACE)
                    .append(alternateColumn).append(SQL_COMMA).append(SQL_SPACE).append(mainColumn).append(SQL_RIGHT_BRACKET)
                    .append(SQL_USING).append(SQL_SPACE).append(SQL_GBK).append(SQL_RIGHT_BRACKET).append(SQL_SPACE).append(sortType);
        }
        return stringBuilder.toString();
    }

}